Imports ccapps.Web.page
Namespace persistent
    Public Class LaPrgUpdate

        Public Shared Function check_contract(ByVal custcode As String, ByVal tmcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(" & tmcode & ",466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function
        Public Shared Function CustomerControlForLaPrg(ByVal custcode As String) As DataTable
            Dim strSQL As String

            Dim PrgCodelist As String
            Dim sqlstr As String = " select s.OLDPRGCODE from EU_BILL.PRGCODE_SHIFT s WHERE CTYPE='C'"
            Dim dts As DataTable = persistent.myDBConn.myORATable(sqlstr)
            For Each rowgpo As DataRow In dts.Rows
                PrgCodelist = PrgCodelist & rowgpo(0) & ","
            Next
            PrgCodelist = PrgCodelist.Substring(0, PrgCodelist.Length - 1)

            strSQL = " select customer_id,prgcode from customer_all where custcode ='" & custcode & "' "
            strSQL += " and cstype='a'and prgcode in (" & PrgCodelist & ")  "
            Return myDBConn.myORATable(strSQL)
        End Function
        Public Shared Function getcustcodeLaPrg(ByVal msisdn As String) As DataTable

            'Begin : BSCS_IX_R3 New Version
            Dim strSQL As String
            strSQL = "Select DECODE(cu2.cslevel, 30, CU3.CUSTCODE, CU2.CUSTCODE) "
            strSQL = strSQL + "FROM contract_all  A, customer_all cu1, customer_all cu2, customer_all cu3 "
            strSQL = strSQL + "WHERE(a.CUSTOMER_ID = CU1.CUSTOMER_ID) "
            strSQL = strSQL + "AND   cu3.customer_id=cu2.customer_id_high "
            strSQL = strSQL + "AND   cu2.customer_id=cu1.customer_id_high "
            strSQL = strSQL + "AND   A.co_id = (select max(B.co_id) "
            strSQL = strSQL + "             from contr_services_cap  B, eu_bill.view_directory_number C "
            strSQL = strSQL + "               where C.dn_num='" & msisdn & "' "
            strSQL = strSQL + "                and B.dn_id=C.dn_id"
            strSQL = strSQL + "               and B.main_dirnum='X')"
            Return myDBConn.myORATable(strSQL)
            'End : BSCS_IX_R3 New Version
        End Function
        Public Shared Function getcustcodeUP(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = "Select DECODE(cu2.cslevel, 30, CU3.CUSTCODE, CU2.CUSTCODE) "
            strSQL += "FROM contract_all  A, customer_all cu1, customer_all cu2, customer_all cu3 "
            strSQL += "WHERE(a.CUSTOMER_ID = CU1.CUSTOMER_ID) "
            strSQL += "AND   cu3.customer_id=cu2.customer_id_high "
            strSQL += "AND   cu2.customer_id=cu1.customer_id_high "
            strSQL += "AND cu1.CUSTCODE ='" & custcode & "' "
            Return myDBConn.myORATable(strSQL)

        End Function

        Public Shared Function checkPrgcodeChange(ByVal custcode As String, ByVal prgcode As String) As Boolean
            Dim strSQL As String
            strSQL = String.Format("select count(*) from mpulknxg t1, customer_all c where t1.tmcode = c.tmcode and c.custcode = '{0}' and t1.prgcode = '{1}' ", custcode, prgcode)

            Return CInt(myDBConn.ORAScalar(strSQL)) > 0
        End Function

        Public Shared Function getAvailablePricegroups(ByVal custcode As String, ByVal prgcode As String) As DataTable
            Dim strSQL As String
            'strSQL = String.Format("select prgcode, prgname from pricegroup_all pga where " & _
            'vbNewLine & "( " & _
            'vbNewLine & "    select case when " & _
            'vbNewLine & "    ( " & _
            'vbNewLine & "        select count(distinct tmcode) from mpulknxg where prgcode = pga.prgcode and tmcode in ( " & _
            'vbNewLine & "            select distinct coa.tmcode from customer_all cua, contract_all coa where coa.customer_id = cua.customer_id and (cua.custcode = '{0}' or custcode like '{0}.%') " & _
            'vbNewLine & "        ) " & _
            'vbNewLine & "    ) = (select count(distinct coa.tmcode) from customer_all cua, contract_all coa where coa.customer_id = cua.customer_id and (cua.custcode = '{0}' or custcode like '{0}.%') " & _
            'vbNewLine & "    then 1 else 0 end Allowed " & _
            'vbNewLine & "    from dual " & _
            'vbNewLine & ") = 1 " & _
            'vbNewLine & "and prgcode != '{1}'", _
            'custcode, prgcode)

            strSQL = String.Format _
            ( _
                "select prgcode, prgname from pricegroup_all pga where " & _
                vbNewLine & "( " & _
                vbNewLine & "    select count(*) from " & _
                vbNewLine & "    ( " & _
                vbNewLine & "        --select distinct nvl(coa.tmcode, -1) tmcode from customer_all cua, contract_all coa where coa.customer_id = cua.customer_id and (cua.custcode = '{0}' or cua.custcode like '{0}.%') " & _
                vbNewLine & "        --union " & _
                vbNewLine & "        select distinct nvl(cua.tmcode, -1) tmcode from customer_all cua where (cua.custcode = '{0}' or cua.custcode like '{0}.%') " & _
                vbNewLine & "    )t " & _
                vbNewLine & "    where tmcode != -1 " & _
                vbNewLine & ") > 0 " & _
                vbNewLine & "and " & _
                vbNewLine & "not exists " & _
                vbNewLine & "( " & _
                vbNewLine & "    select tmcode from " & _
                vbNewLine & "    ( " & _
                vbNewLine & "        --select distinct nvl(coa.tmcode, -1) tmcode from customer_all cua, contract_all coa where coa.customer_id = cua.customer_id and (cua.custcode = '{0}' or cua.custcode like '{0}.%') " & _
                vbNewLine & "        --union " & _
                vbNewLine & "        select distinct nvl(cua.tmcode, -1) tmcode from customer_all cua , contract_all co  where (cua.custcode = '{0}' or cua.custcode like '{0}.%') " & _
                vbNewLine & "    and cua.customer_id = co.customer_id   and co.ch_status != 'd' " & _
                vbNewLine & "    union " & _
                vbNewLine & "  select distinct nvl(cua.tmcode, -1) tmcode from customer_all cua  where  cua.custcode =  '{0}' " & _
                vbNewLine & "    )t " & _
                vbNewLine & "    where tmcode != -1 " & _
                vbNewLine & "    minus " & _
                vbNewLine & "    select distinct tmcode from mpulknxg m1 where m1.prgcode = pga.prgcode " & _
                vbNewLine & ") " & _
                vbNewLine & "and prgcode != '{1}' ", _
                custcode, prgcode _
            )


            Return myDBConn.myORATable(strSQL)
        End Function


        Public Shared Function updatePrgCode(ByVal custcode As String, ByVal prgcode As String) As Long
            Dim strSQL As String
            strSQL = " update customer_all set prgcode='" & prgcode & "', csmoddate = sysdate where custcode like '" & custcode & ".%' or custcode='" & custcode & "' "
            Return myDBConn.ORAExecute(strSQL)
        End Function
        Public Shared Function check_contract_activatedDays(ByVal custcode As String, ByVal tmcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "
            strSQL = strSQL + " and to_number((sysdate-co_activated))<(select days from rateplan_days where tmcode=co.tmcode)"

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function check_contract_mobilData(ByVal custcode As String, ByVal tmcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(" & tmcode & ",466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "
            strSQL = strSQL + " and co_activated > add_months(sysdate,-3)"


            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function check_mobilData(ByVal custcode As String) As Integer
            Dim strSQL As String
            strSQL = " select count(*)  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode in (483) "
            strSQL = strSQL + " and cur.ch_status ='a' "

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function get_contract(ByVal custcode As String, ByVal tmcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select co.co_id, co.co_itemized_bill, co.CO_OPERATOR_DIR  from customer_all cu, contract_all co, curr_co_status cur "
            strSQL = strSQL + " where cu.custcode = '" & custcode & "' "
            strSQL = strSQL + " and co.customer_id=cu.customer_id "
            strSQL = strSQL + " and cur.co_id=co.co_id "
            strSQL = strSQL + " and co.tmcode not in(" & tmcode & ",466,483) "
            strSQL = strSQL + " and cur.ch_status ='a' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function GetContracts(ByVal custcode As String) As DataTable
            Dim sql As String
            sql = "select v1.* from ("
            sql += " select v.*, (select ch_status from curr_co_status where co_id=v.co_id ) statu "
            sql += " from ( "
            sql += " select distinct cslevel,custcode,fn_coid_to_msisdn(co.co_id) ms,co.co_id,co.co_activated,cu.PRGCODE from customer_all cu, contract_all co "
            sql += " where (cu.custcode like  '" & custcode & ".%' or cu.custcode='" & custcode & "' )  "
            sql += " and cu.customer_id=co.customer_id"
            sql += " and cu.cslevel='40'"
            sql += " ) v  ) v1 where v1.statu in ('a','s') "

            Return myDBConn.myORATable(sql)

        End Function

        Public Shared Function get_prgcode(ByVal tmcode As String) As DataTable
            Dim strSQL As String

            strSQL = " select prgcode from mpulknxg where tmcode= " & tmcode
            strSQL = strSQL + " and prgcode !=27 and rownum<2 "

            Return myDBConn.myORATable(strSQL)
        End Function
        Public Shared Function get_service(ByVal coid As String, ByVal tmcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select sncode,spcode from contr_services where co_id=" & coid
            strSQL = strSQL + " and sncode not in( "
            strSQL = strSQL + " select sncode from mpulktmb m where tmcode = " & tmcode
            strSQL = strSQL + " and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=m.tmcode) "
            strSQL = strSQL + "  ) "
            strSQL = strSQL + " and cs_stat_chng like '%a' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_service_diff(ByVal tm1 As String, ByVal tm2 As String) As DataTable
            Dim strSQL As String
            strSQL = " select sncode from mpulktmb m where tmcode=" & tm1
            strSQL = strSQL + " and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=m.tmcode) "
            strSQL = strSQL + " and csind='X' "
            strSQL = strSQL + " minus "
            strSQL = strSQL + " select sncode from mpulktmb m where tmcode=" & tm2
            strSQL = strSQL + " and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=m.tmcode) "
            strSQL = strSQL + " and csind='X' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_valide_prg(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select prgcode from customer_all where custcode='" & custcode & "' "
            Return myDBConn.myORATable(strSQL)
        End Function
        Public Shared Function get_valid_prg_value(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select cus.prgcode ,prg_all.prgname from customer_all cus ,PRICEGROUP_ALL prg_all"
            strSQL += " where cus.custcode='" & custcode & "'"
            strSQL += " and cus.PRGCODE=prg_all.PRGCODE "

            Return myDBConn.myORATable(strSQL)
        End Function
        Public Shared Function get_valid_prg_value1(ByVal custcode As String) As String
            Dim strSQL As String
            strSQL = " select cus.prgcode from customer_all cus ,PRICEGROUP_ALL prg_all"
            strSQL += " where cus.custcode='" & custcode & "'"
            strSQL += " and cus.PRGCODE=prg_all.PRGCODE "

            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function
        Public Shared Function get_valid_prg_value2(ByVal OLDPRGCODE As Integer) As Integer
            Dim strSQL As String
            strSQL = " SELECT  NEWPRGCODE FROM EU_BILL.PRGCODE_SHIFT WHERE OLDPRGCODE=" & OLDPRGCODE & " "
            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function

        Public Shared Function get_update_prg_value(ByVal prgcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select prgcode ,prgname from PRICEGROUP_ALL "
            strSQL += " where prgcode=" & prgcode & ""


            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_tmcode() As DataTable
            Dim strSQL As String
            strSQL = " select tmcode,des from mputmtab m1 where tmcode in(261,262,263,465,464) "
            strSQL = strSQL + " and vscode =(select max(vscode) from mputmtab where tmcode =m1.tmcode) "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function get_tmcode_history(ByVal coid As String) As DataTable
            Dim strSQL As String
            strSQL = " select distinct tmcode,cs_seqno, "
            strSQL += " (select des from mputmtab m where tmcode=cs.tmcode and vscode=(select max(vscode) from mputmtab where tmcode=m.tmcode and status='P') ) "
            strSQL += " from contr_services cs where co_id =" & coid
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_User_Count(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String
            strSQL = " select trunc(tarih) tarih,username,count(*) count from batch_req where job_id='JB2' "
            strSQL += " and trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' group by trunc(tarih),username"

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_Proc_Count(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String
            strSQL = " select trunc(tarih) tarih,count(*) count from batch_req where job_id='JB2' "
            strSQL += " and trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' group by trunc(tarih)"

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_Error_Proc(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String

            strSQL = " select b1.tarih,b1.username,b1.req_id, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=1) sirket, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=2) tarife from batch_req b1 where "
            strSQL += " trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' "
            strSQL += " and b1.status=2 "
            strSQL += " and b1.job_id='JB2' "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function Get_Firm_Tmcode(ByVal tarih_baslangic As String, ByVal tarih_bitis As String) As DataTable
            Dim strSQL As String

            strSQL = "  select t3.tarih,t3.sirket,t3.old_tmcode, "
            strSQL += " (select des from mputmtab where tmcode=t3.old_tmcode and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=t3.old_tmcode)) old_tmcode_desc, "
            strSQL += " t3.new_tmcode, "
            strSQL += " (select des from mputmtab where tmcode=t3.new_tmcode and vscode=(select max(vscode) from mpulktmb m1 where m1.tmcode=t3.new_tmcode)) new_tmcode_desc, t3.count "
            strSQL += " from ( "
            strSQL += " select t2.tarih,t2.sirket, t2.old_tmcode,t2.tarife new_tmcode,count(*) count  from ( "
            strSQL += " select m1.*,ct.co_id, "
            strSQL += " (select distinct tmcode from RATEPLAN_HIST r where co_id=ct.co_id "
            strSQL += " and seqno = (select max(seqno)-1 from rateplan_hist where co_id=ct.co_id)) old_tmcode "
            strSQL += " from "
            strSQL += " ( "
            strSQL += " select t1.*,ca.customer_id from "
            strSQL += " ( "
            strSQL += " select b1.tarih, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=1) sirket, "
            strSQL += " (select value from batch_req_param where req_id=b1.req_id and parameterno=2) tarife from batch_req b1 where "
            strSQL += " trunc(tarih) between '" & tarih_baslangic & "' and '" & tarih_bitis & "' "
            strSQL += " and b1.status=1"
            strSQL += " and b1.job_id='JB2' "
            strSQL += " ) t1,customer_all ca "
            strSQL += " where(t1.sirket = ca.custcode) "
            strSQL += " ) m1,contract_all ct where m1.customer_id=ct.customer_id "
            strSQL += " and m1.tarife=ct.tmcode "
            strSQL += " )t2 where t2.old_tmcode is not null  "
            strSQL += " group by t2.tarih,t2.sirket,t2.old_tmcode,t2.tarife  "
            strSQL += " ) t3 "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function CustomerControlForMassPrg(ByVal custcode As String) As DataTable
            Dim strSQL As String
            Dim PrgCodelist As String
            Dim sqlstr As String = " select s.OLDPRGCODE from EU_BILL.PRGCODE_SHIFT s WHERE CTYPE='M'"
            Dim dts As DataTable = persistent.myDBConn.myORATable(sqlstr)
            For Each rowgpo As DataRow In dts.Rows
                PrgCodelist = PrgCodelist & rowgpo(0) & ","
            Next
            PrgCodelist = PrgCodelist.Substring(0, PrgCodelist.Length - 1)
            strSQL = " select customer_id,prgcode from customer_all where custcode ='" & custcode & "' "
            strSQL += " and cstype='a'and prgcode in (" & PrgCodelist & ")  "
            'LOGLAMA
            'Return myDBConn.myORATable(strSQL)
            Dim gen As Object = New MassPrgUpdate
            Return myDBConn.myORATable_LOG(strSQL, custcode, "", "", "", "Select(CustomerControlForMassPrg)", "LaPrgUpdate", gen.Session("user"))
        End Function
        Public Shared Function getcustcodeMassPrg(ByVal msisdn As String) As DataTable

            Dim strsql As String = "select custcode from customer_all where custcode=fn_msisdn_to_custcode('" & msisdn & "')"
            'LOGLAMA
            ' Return myDBConn.myORATable(strsql)
            Dim gen As Object = New MassPrgUpdate()
            Return myDBConn.myORATable_LOG(strsql, "", "", "", msisdn, "Select(getcustcodeMassPrg)", "LaPrgUpdate", gen.Session("user"))
        End Function
        Public Shared Function getmsisdn(ByVal cusctcode As String) As String

            Dim strsql As String = "select fn_custid_to_msisdn (c.customer_id) from customer_all  c where c.custcode='" & cusctcode & "'"
            Return myDBConn.ORAScalar(strsql)
        End Function
        Public Shared Function getCustId(ByVal custCode As String) As Integer

            Dim strsql As String = "select c.customer_id from customer_all  c where c.custcode='" & custCode & "'"
            Return myDBConn.ORAScalar(strsql)
        End Function

        Public Shared Function insertPrgUpdateLog(ByVal oldPriceGrp As String, ByVal newPriceGrp As String, ByVal custCode As String, ByVal userCode As String) As Long
            Dim customerId As Integer = getCustId(custCode)
            Dim strSQL As String

            strSQL = "insert into eu_bill.la_prgcode_update_log (old_pricegrp, new_pricegrp, customer_id, usercode) "
            strSQL += "values  ('" & oldPriceGrp & "','" & newPriceGrp & "'," & customerId & ",'" & userCode & "')"

            Return myDBConn.ORAExecute(strSQL)
        End Function

    End Class
End Namespace
